﻿module System.Extensibility.Data.SqlServerModel.Kilimanjaro.Script.Create
open System
open System.Text
open System.Extensibility.Data.SqlServerModel.Kilimanjaro
open fs
open Base
let typ (v: Type) =
    let b = StringBuilder()
    b.Append (v.GetType().Name) |> ignore
    if v :? Decimal then b.AppendFormat("({0}, {1})", (v :?> Decimal).Precision, (v :?> Decimal).Scale) |> ignore
    elif (v :? CharBase) then
        let charType = v :?> CharBase
        b.AppendFormat("(" + (if charType.Length = charType.MaxLength then "MAX" else charType.Length.ToString()) + ")") |> ignore
    b.ToString()
let field (v: Field) =
    if v.Value = null then "NULL"
    else
        let r = v
        if (typeof<CharBase>.IsAssignableFrom(r.Type.GetType()) || r.Type.GetType() = typeof<NText>) then "N'" + (string v.Value).Replace("'", "''") + "'"
        elif r.Type.GetType() = typeof<UniqueIdentifier> then "'" + string v.Value + "'"
        elif r.Type.GetType() = typeof<DateTime> then "'" + (v.Value :?> System.DateTime).ToString() + "'"
        else v.Value.ToString()
let row (v: Row , includeIdColumns: bool) = String.Concat((if includeIdColumns then v.Fields else v.Fields |> Seq.skip 1) |> Seq.map (fun p -> ", " + field p)).Substring(2)
let tableRows (v: Table) =
    let includeIdColumns = true
    let columnNames = (if includeIdColumns then v.Columns else v.Columns |> Seq.skip 1) |> Seq.map(fun p -> p.Name)
    let name = v.FullName
    let fieldsString = (columnNames |> Seq.fold (fun a b -> a + ", [" + b + "]") "").Substring(2)
    let rows = String.Concat(v.Rows |> Seq.map(fun p -> "," + br + "(" + row (p, includeIdColumns) + ")") |> Seq.toArray).Substring(1)
    String.Format((if includeIdColumns then "SET IDENTITY_INSERT {1} ON{0}GO{0}" else "") + "INSERT INTO {1} (" + fieldsString + ") VALUES " + rows + ";{0}" + (if includeIdColumns then "SET IDENTITY_INSERT {1} OFF{0}GO{0}" else ""), br, name)
//public SqlCommand BuildCommand()
//{
//    var r = new SqlCommand(BuildScript());
//    r.Parameters.AddRange(Value.Fields.Select(p => new SqlParameter("@" + p.Name, p.Value)).ToArray());
//    return r;
//}
let column (v: Column) =
    let b = StringBuilder()
    let name = v.Name
    b.AppendFormat("\t[{0}] {1}", name, typ v.Type) |> ignore
    if not v.AllowNulls then b.Append " NOT NULL" |> ignore
    if v.IsIdentity then b.Append " IDENTITY" |> ignore
    b.Append "," |> ignore
    b.AppendLine() |> ignore
    //if (column.Name == "Id")
    //{
    //    b.AppendFormat("\t[{1}] {2} NOT NULL,{0}", br, name, "[int] IDENTITY(1,1)");
    //}
    //else if (column.Name == "Guid")
    //{
    //    b.AppendFormat("\t[{1}] {2} NOT NULL CONSTRAINT [DF_{3}_Guid]  DEFAULT (newid()),{0}", br, name, "[uniqueidentifier] ROWGUIDCOL ", GuidAttribute.GetValue(column.ReflectedType).ToString());
    //}
    //else if (typeof(EntityBase).IsAssignableFrom(column.PropertyType) || typeof(IEntityDescriptor).IsAssignableFrom(column.PropertyType))
    //{
    //    //b.AppendFormat("\t[{1}] {2} NULL,{0}", n, propertyInfo.PropertyType.Name.Remove(0, 1) + "Guid", "[uniqueidentifier]");
    //    b.AppendFormat("\t[{1}] {2} NULL,{0}", br, name, "[uniqueidentifier]");
    //}
    //else
    //{
    //    b.AppendFormat("\t[{1}] {2} NULL,{0}", br, name, GetTypeScript(column));
    //}
    b.ToString()
let association (association: AssociationColumn, declaringTable: Table) =
    String.Format(
        "ALTER TABLE " + declaringTable.FullName + "{0}" +
        "  ADD CONSTRAINT [FK_" + declaringTable.Name + "_" + association.ForeignTable.Name + "_" + association.Name + "] FOREIGN KEY ([" + association.Name + "]) REFERENCES " + association.ForeignTable.FullName + "([" + association.ForeignTable.PrimaryKeyName + "]){0}" +
        "  ON DELETE NO ACTION{0}", br)
    //Katmai.Association Member { get; set; }
    //public Association(Katmai.Association member)
    //{
    //    Member = member;
    //    StringBuilder b = new StringBuilder();
    //    AppendForeignKey(b, Member);
    //    Content = b.ToString();
    //}
    //static void AppendForeignKey(StringBuilder b, Katmai.Association association)
    //{
    //    b.AppendFormat(
    //        "ALTER TABLE " + association.Table.FullName + "{0}" +
    //        "  ADD CONSTRAINT [" + association.Name + "] FOREIGN KEY ([" + association.ThisKey + "]) REFERENCES " + association.ForeignTable.FullName + "([" + association.ThisKey + "]){0}" +
    //        "  ON DELETE NO ACTION{0}", br);
    //}
let table (table: Table, isOneTableSchema: bool, useRealObjectNames: bool, schemaName: string) =
    let b = StringBuilder()
    let name = table.Name
    let guid = if useRealObjectNames then "DepartmentGuid" else "68B70CAA-5742-4327-ADDB-D76FA150E597"
    let userGuid = if useRealObjectNames then "UserGuid" else "0DCA4FEF-AFD2-4a61-9F36-7E00A47AA3EC"
    let key = table.PrimaryKey |> Seq.map (fun a -> sprintf "[%s] ASC" a.Name) |> String.concat ("," + br + "\t")
    b.AppendFormat("CREATE TABLE {0}({1}", table.FullName, br) |> ignore
    if (isOneTableSchema) then b.AppendFormat("\t[{0}] {1} NULL,{2}", "Discriminator", "[nvarchar](450)", br) |> ignore
    b.Append(String.Concat(table.Columns |> Seq.map (fun p -> column(p)) |> Seq.toArray)) |> ignore
    b.AppendFormat(
        " CONSTRAINT [PK_{0}_1] PRIMARY KEY CLUSTERED {2}({2}	{1}{2}" +
        ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]{2}" +
        ") ON [PRIMARY]{2}{2}GO{2}", name, key, br) |> ignore
    if (isOneTableSchema) then
        b.AppendFormat(
            "ALTER TABLE [{1}].[{4}]  WITH CHECK ADD  CONSTRAINT [FK_{4}_{3}] FOREIGN KEY([{3}]){0}" +
            "REFERENCES [{1}].[{4}] ([{2}]){0}GO{0}" +
            "ALTER TABLE [{1}].[{4}] CHECK CONSTRAINT [FK_{4}_{3}]{0}GO{0}", br, table.Name, key, guid, name) |> ignore
        b.AppendFormat(
            "ALTER TABLE [{1}].[{2}]  WITH CHECK ADD  CONSTRAINT [FK_{2}_aspnet_Users] FOREIGN KEY([{3}]){0}" +
            "REFERENCES [dbo].[aspnet_Users] ([UserId]){0}GO{0}" +
            "ALTER TABLE [{1}].[{2}] CHECK CONSTRAINT [FK_{2}_aspnet_Users]{0}GO{0}", br, table.Name, name, userGuid) |> ignore
    if (table.Rows <> null && not (table.Rows |> Seq.isEmpty)) then b.Append(tableRows(table)) |> ignore
    b.ToString()
//    let Table2 v = table(v, false, false, null)
//    let Table3 (v, isOneTableSchema, useRealObjectNames, schemaName) = table(v, false, useRealObjectNames, schemaName)
let schema (v: Schema) = "CREATE SCHEMA [" + v.Name + "] AUTHORIZATION [dbo]" + br + "GO" + br
let database (database: Database) =
    let useRealObjectNames = false
    let schemaName = null
    let isOneTableSchema = false
    let b = StringBuilder()
    b.AppendFormat ("SET ANSI_NULLS ON{0}GO{0}SET QUOTED_IDENTIFIER ON{0}GO{0}", br) |> ignore
    b.Append (String.Concat (database.Schemas |> Seq.filter (fun a -> a.Name <> Schema.``default``.Name) |> Seq.map (fun p -> schema p) |> Seq.toArray)) |> ignore
    if isOneTableSchema then failwith "//AppendTable(b, database.Tables.Single(p => p.Name == \"Class1\"), isOneTableSchema);"
    else b.Append (String.Concat(database.Tables |> Seq.map (fun p -> (table(p, isOneTableSchema, useRealObjectNames, schemaName))) |> Seq.toArray)) |> ignore
    //b.Append(string.Concat(database.Tables.SelectMany(p => p.ForeignAssociations.Select(q => new Association(q).ToString())).ToArray()));
    b.Append (String.Concat (database.Tables |> Seq.collect (fun declaringTable -> declaringTable.Columns |> Seq.ofType<AssociationColumn> |> Seq.map (fun p -> association (p, declaringTable))) |> Seq.toArray)) |> ignore
    b.ToString()
//namespace System.Extensibility.Data.SqlServerModel.Katmai
//module Map =
//    open Microsoft.SqlServer.Management.Smo
//    open CodeDom
//    let typ (v: DataType) =
//        match v.SqlDataType with
//        | SqlDataType.Bit -> typeof<bool>
//        | SqlDataType.BigInt | SqlDataType.Int | SqlDataType.SmallInt -> typeof<int>
//        | SqlDataType.Date | SqlDataType.DateTime | SqlDataType.DateTime2 -> typeof<DateTime>
//        | SqlDataType.UniqueIdentifier -> typeof<Guid>
//        | _ -> typeof<string>
//    let build (d: string) =
//        let s = Server()
//        let at = Seq.empty<Attribute>
//        let records = (s.Databases.[d]).Tables |> Seq.cast<Table> |> Seq.map (fun a -> { Name = a.Name; Values = a.Columns |> Seq.cast<Column> |> Seq.map (fun a -> { Name = a.Name; Type = typ a.DataType; Attributes = at }); Attributes = at })
//        let r = { Name = d; Enumerations = Seq.empty<Enumeration>; Records = records }
//        Mapping.CodeGereation.``module`` r
//namespace System.Extensibility.Data.SqlServerModel.Katmai
//module Physical =
//    open System.Data.SqlClient
//    open System.Text
//    module Table =
//        let list d = seq {
//            let table = Table (Name = "TABLES", Columns = Seq.singleton(Column("TABLE_NAME")), Schema = Schema "INFORMATION_SCHEMA", Database = Database d)
//            let r = (new SqlCommand(TableScript(table).Select + " WHERE [TABLE_TYPE] = 'BASE TABLE'", createSqlConnection d)).ExecuteReader()
//            while r.Read() do yield string r.[0] }
//    module Database =
//        type r = { Name: string; Connection: string; Tables: seq<string> }
//        let list _ = seq {
//            let table = Table (Name = "Databases", Schema = Schema "sys", Columns = Seq.singleton(Column("Name")))
//            let r = (new SqlCommand(TableScript(table).Select, master)).ExecuteReader()
//            while r.Read() do
//                let a =  string r.[0]
//                yield { Name = a; Connection = sprintf @"Data Source=.;Database=%s;Integrated Security=true" a; Tables = Table.list a } }
//    module Server =
//        open Database
//        let databases = list Some |> Seq.map (fun a -> { Name = a; Connection = sprintf @"Data Source=.;Database=%s;Integrated Security=true" a; Tables = Table.list a })
module Test =
    open fs
    open System.Extensibility.Data.SqlServerModel.Kilimanjaro.Descriptor.Samples
    let guestbook _ = assert (database guestbook = File.Text.readAll @"E:\p\xb\200904230053, Katmai\200904232356, fix\Katmai\Katmai.Test\Resources\Guestbook.sql.txt")
    let nameCards _ = assert (database nameCards = File.Text.readAll @"E:\p\xb\200904230053, Katmai\200904232356, fix\Katmai\Katmai.Test\Resources\NameCards.sql.txt")
    let adventureWorks _ = assert (database adventureWorks = File.Text.readAll @"..\..\..\Data\Kilimanjaro\ExpectedGeneratedScript.sql.txt")